JeeSite的Excel导入、导出、支持大数据量，使用annotation最小化配置

介绍：

对Apache POI 3.9的简单封装，实现Excel的导出导入功能。使用Annotation定义导出导入字段。http://jeesite.com

优点：

1. 简单易用，支持大数量导出，配置简单，代码量少。
2. 支持Excel 2003、2007、2010（xls、xlsx）格式。
3. 支持简单格式设置，对齐方式，排序等
4. 可导出字典类型数据，自定义数据字段类型（例如：部门关联对象，部门名称与部门编号互转）。
5. 无需建立导入模板，系统自动生成。

缺点：

1. 格式单一，无法导出格式比较复杂的表格。
2. 不能使用模板进行导入，导出。

分析开始 ：

后台框架：SpringMVC+mybatis

1 前台jsp界面：

<%@ page contentType="text/html;charset=UTF-8" %><%@ include file="/WEB-INF/views/include/taglib.jsp"%>		
    		
    
		
     
      
			
      
      
      
　　			
      			
      下载模板		
     
	
    
	
    
		
     
用户列表
		
     
      
用户添加
     	
    
	
    		
     		
     		
     		
     
			
      
归属公司：
       
			
      
登录名：
       
			
      
			
      
归属部门：
       
			
      
姓   名：
       
			
      
								
			
      
		
     
	
    	
    	
    
    			
    
    
    
<%--
 --%>
<%--				
 --%>				
		     
      
       
归属公司
       
归属部门
       
登录名
       
姓名
       
电话
       
手机
       
角色
       
操作
      
     
		     
		      
				       
${user.company.name}
				       
${user.office.name}
				       
${user.loginName}
				       
${user.name}
				       
${user.phone}
				       
${user.mobile}
       
${user.roleNames}
       
    				修改					删除				
			      
				     
	    
	
    
     ${page}
    

2：提交到controller

/** * Copyright © 2012-2014 JeeSite All rights reserved. */package com.thinkgem.jeesite.modules.sys.web;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.validation.ConstraintViolationException;import org.apache.shiro.authz.annotation.RequiresPermissions;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.ModelAttribute;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.servlet.mvc.support.RedirectAttributes;import com.google.common.collect.Lists;import com.google.common.collect.Maps;import com.thinkgem.jeesite.common.beanvalidator.BeanValidators;import com.thinkgem.jeesite.common.config.Global;import com.thinkgem.jeesite.common.persistence.Page;import com.thinkgem.jeesite.common.utils.DateUtils;import com.thinkgem.jeesite.common.utils.StringUtils;import com.thinkgem.jeesite.common.utils.excel.ExportExcel;import com.thinkgem.jeesite.common.utils.excel.ImportExcel;import com.thinkgem.jeesite.common.web.BaseController;import com.thinkgem.jeesite.modules.sys.entity.Office;import com.thinkgem.jeesite.modules.sys.entity.Role;import com.thinkgem.jeesite.modules.sys.entity.User;import com.thinkgem.jeesite.modules.sys.service.SystemService;import com.thinkgem.jeesite.modules.sys.utils.UserUtils;/** * 用户Controller * @author ThinkGem * @version 2013-8-29 */@Controller@RequestMapping(value = "${adminPath}/sys/user")public class UserController extends BaseController {	@Autowired	private SystemService systemService;		@ModelAttribute	public User get(@RequestParam(required=false) String id) {		if (StringUtils.isNotBlank(id)){			return systemService.getUser(id);		}else{			return new User();		}	}	@RequiresPermissions("sys:user:view")	@RequestMapping(value = {"index"})	public String index(User user, Model model) {		return "modules/sys/userIndex";	}	@RequiresPermissions("sys:user:view")	@RequestMapping(value = {"list", ""})	public String list(User user, HttpServletRequest request, HttpServletResponse response, Model model) {		Page
    
      page = systemService.findUser(new Page
     
      (request, response), user);        model.addAttribute("page", page);		return "modules/sys/userList";	}		@ResponseBody	@RequiresPermissions("sys:user:view")	@RequestMapping(value = {"listData"})	public Page
      
        listData(User user, HttpServletRequest request, HttpServletResponse response, Model model) {		Page
       
         page = systemService.findUser(new Page
        
         (request, response), user);		return page;	}	@RequiresPermissions("sys:user:view")	@RequestMapping(value = "form")	public String form(User user, Model model) {		if (user.getCompany()==null || user.getCompany().getId()==null){			user.setCompany(UserUtils.getUser().getCompany());		}		if (user.getOffice()==null || user.getOffice().getId()==null){			user.setOffice(UserUtils.getUser().getOffice());		}		model.addAttribute("user", user);		model.addAttribute("allRoles", systemService.findAllRole());		return "modules/sys/userForm";	}	@RequiresPermissions("sys:user:edit")	@RequestMapping(value = "save")	public String save(User user, HttpServletRequest request, Model model, RedirectAttributes redirectAttributes) {		if(Global.isDemoMode()){			addMessage(redirectAttributes, "演示模式，不允许操作！");			return "redirect:" + adminPath + "/sys/user/list?repage";		}		// 修正引用赋值问题，不知道为何，Company和Office引用的一个实例地址，修改了一个，另外一个跟着修改。		user.setCompany(new Office(request.getParameter("company.id")));		user.setOffice(new Office(request.getParameter("office.id")));		// 如果新密码为空，则不更换密码		if (StringUtils.isNotBlank(user.getNewPassword())) {			user.setPassword(SystemService.entryptPassword(user.getNewPassword()));		}		if (!beanValidator(model, user)){			return form(user, model);		}		if (!"true".equals(checkLoginName(user.getOldLoginName(), user.getLoginName()))){			addMessage(model, "保存用户'" + user.getLoginName() + "'失败，登录名已存在");			return form(user, model);		}		// 角色数据有效性验证，过滤不在授权内的角色		List
         
           roleList = Lists.newArrayList(); List
          
            roleIdList = user.getRoleIdList(); for (Role r : systemService.findAllRole()){ if (roleIdList.contains(r.getId())){ roleList.add(r); } } user.setRoleList(roleList); // 保存用户信息 systemService.saveUser(user); // 清除当前用户缓存 if (user.getLoginName().equals(UserUtils.getUser().getLoginName())){ UserUtils.clearCache(); //UserUtils.getCacheMap().clear(); } addMessage(redirectAttributes, "保存用户'" + user.getLoginName() + "'成功"); return "redirect:" + adminPath + "/sys/user/list?repage"; } @RequiresPermissions("sys:user:edit") @RequestMapping(value = "delete") public String delete(User user, RedirectAttributes redirectAttributes) { if(Global.isDemoMode()){ addMessage(redirectAttributes, "演示模式，不允许操作！"); return "redirect:" + adminPath + "/sys/user/list?repage"; } if (UserUtils.getUser().getId().equals(user.getId())){ addMessage(redirectAttributes, "删除用户失败, 不允许删除当前用户"); }else if (User.isAdmin(user.getId())){ addMessage(redirectAttributes, "删除用户失败, 不允许删除超级管理员用户"); }else{ systemService.deleteUser(user); addMessage(redirectAttributes, "删除用户成功"); } return "redirect:" + adminPath + "/sys/user/list?repage"; } /** * 导出用户数据 * @param user * @param request * @param response * @param redirectAttributes * @return */ @RequiresPermissions("sys:user:view") @RequestMapping(value = "export", method=RequestMethod.POST) public String exportFile(User user, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) { try { String fileName = "用户数据"+DateUtils.getDate("yyyyMMddHHmmss")+".xlsx"; Page
           
             page = systemService.findUser(new Page
            
             (request, response, -1), user); new ExportExcel("用户数据", User.class).setDataList(page.getList()).write(response, fileName).dispose(); return null; } catch (Exception e) { addMessage(redirectAttributes, "导出用户失败！失败信息："+e.getMessage()); } return "redirect:" + adminPath + "/sys/user/list?repage"; } /** * 导入用户数据 * @param file * @param redirectAttributes * @return */ @RequiresPermissions("sys:user:edit") @RequestMapping(value = "import", method=RequestMethod.POST) public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) { if(Global.isDemoMode()){ addMessage(redirectAttributes, "演示模式，不允许操作！"); return "redirect:" + adminPath + "/sys/user/list?repage"; } try { int successNum = 0; int failureNum = 0; StringBuilder failureMsg = new StringBuilder(); ImportExcel ei = new ImportExcel(file, 1, 0); List
             
               list = ei.getDataList(User.class); for (User user : list){ try{ if ("true".equals(checkLoginName("", user.getLoginName()))){ user.setPassword(SystemService.entryptPassword("123456")); BeanValidators.validateWithException(validator, user); systemService.saveUser(user); successNum++; }else{ failureMsg.append("
              
登录名 "+user.getLoginName()+" 已存在; "); failureNum++; } }catch(ConstraintViolationException ex){ failureMsg.append("
              
登录名 "+user.getLoginName()+" 导入失败："); List
              
                messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": "); for (String message : messageList){ failureMsg.append(message+"; "); failureNum++; } }catch (Exception ex) { failureMsg.append("
               
登录名 "+user.getLoginName()+" 导入失败："+ex.getMessage()); } } if (failureNum>0){ failureMsg.insert(0, "，失败 "+failureNum+" 条用户，导入信息如下："); } addMessage(redirectAttributes, "已成功导入 "+successNum+" 条用户"+failureMsg); } catch (Exception e) { addMessage(redirectAttributes, "导入用户失败！失败信息："+e.getMessage()); } return "redirect:" + adminPath + "/sys/user/list?repage"; } /** * 下载导入用户数据模板 * @param response * @param redirectAttributes * @return */ @RequiresPermissions("sys:user:view") @RequestMapping(value = "import/template") public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) { try { String fileName = "用户数据导入模板.xlsx"; List
               
                 list = Lists.newArrayList(); list.add(UserUtils.getUser()); new ExportExcel("用户数据", User.class, 2).setDataList(list).write(response, fileName).dispose(); return null; } catch (Exception e) { addMessage(redirectAttributes, "导入模板下载失败！失败信息："+e.getMessage()); } return "redirect:" + adminPath + "/sys/user/list?repage"; } /** * 验证登录名是否有效 * @param oldLoginName * @param loginName * @return */ @ResponseBody @RequiresPermissions("sys:user:edit") @RequestMapping(value = "checkLoginName") public String checkLoginName(String oldLoginName, String loginName) { if (loginName !=null && loginName.equals(oldLoginName)) { return "true"; } else if (loginName !=null && systemService.getUserByLoginName(loginName) == null) { return "true"; } return "false"; } /** * 用户信息显示及保存 * @param user * @param model * @return */ @RequiresPermissions("user") @RequestMapping(value = "info") public String info(User user, HttpServletResponse response, Model model) { User currentUser = UserUtils.getUser(); if (StringUtils.isNotBlank(user.getName())){ if(Global.isDemoMode()){ model.addAttribute("message", "演示模式，不允许操作！"); return "modules/sys/userInfo"; } currentUser.setEmail(user.getEmail()); currentUser.setPhone(user.getPhone()); currentUser.setMobile(user.getMobile()); currentUser.setRemarks(user.getRemarks()); currentUser.setPhoto(user.getPhoto()); systemService.updateUserInfo(currentUser); model.addAttribute("message", "保存用户信息成功"); } model.addAttribute("user", currentUser); model.addAttribute("Global", new Global()); return "modules/sys/userInfo"; } /** * 返回用户信息 * @return */ @RequiresPermissions("user") @ResponseBody @RequestMapping(value = "infoData") public User infoData() { return UserUtils.getUser(); } /** * 修改个人用户密码 * @param oldPassword * @param newPassword * @param model * @return */ @RequiresPermissions("user") @RequestMapping(value = "modifyPwd") public String modifyPwd(String oldPassword, String newPassword, Model model) { User user = UserUtils.getUser(); if (StringUtils.isNotBlank(oldPassword) && StringUtils.isNotBlank(newPassword)){ if(Global.isDemoMode()){ model.addAttribute("message", "演示模式，不允许操作！"); return "modules/sys/userModifyPwd"; } if (SystemService.validatePassword(oldPassword, user.getPassword())){ systemService.updatePasswordById(user.getId(), user.getLoginName(), newPassword); model.addAttribute("message", "修改密码成功"); }else{ model.addAttribute("message", "修改密码失败，旧密码错误"); } } model.addAttribute("user", user); return "modules/sys/userModifyPwd"; } @RequiresPermissions("user") @ResponseBody @RequestMapping(value = "treeData") public List